﻿using DevExpress.Utils.CommonDialogs;
using DevExpress.XtraEditors;
using MiniExcelLibs;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.IO;
using System.Linq;
using System.Text;
using System.Windows.Forms;

namespace DXApplication1月报表
{
    public partial class Form1 : DevExpress.XtraEditors.XtraForm
    {

        public static string conn = "Data Source=192.168.1.49;DataBase=UFDATA_009_2022;uid=bb;pwd=qwerasdf@";
        public static DataTable table = new DataTable();
        public Form1()
        {
            InitializeComponent();
        }

        private void simpleButton查询_Click(object sender, EventArgs e)
        {
            gridView1.Columns.Clear();
            gridView1.OptionsView.ShowFooter = false;
            if (radioGroup1.EditValue.ToString() == "明细")
            {
                明细查询();
            }
            if (radioGroup1.EditValue.ToString() == "汇总1")
            {
                汇总1查询();
            }
            if (radioGroup1.EditValue.ToString() == "汇总2")
            {
                
                汇总2查询();
            }
            if (radioGroup1.EditValue.ToString() == "汇总3")
            {
                gridView1.OptionsView.ShowFooter = true;
                汇总3查询();
            }
            //foreach (DevExpress.XtraGrid.Columns.GridColumn column in gridView1.Columns)
            //{
            //    column.AppearanceCell.TextOptions.HAlignment = DevExpress.Utils.HorzAlignment.Center;
            //}
            MessageBox.Show("查询结束!");
            
        }
        private void 明细查询()
        {
            string tbname = "[Temp" + Guid.NewGuid().ToString() + "]";

            string sql = @" SELECT     账套,'U8 ' 类型, dPODate, csocode, cInvCode,'88888888888888888888888888888888888' 费用类型,'T0' tx, cInvName, cInvStd, iQuantity, iUnitPrice, isum,isum ko_sch_amt,isum log_act_amt into " + tbname;
            sql += " FROM      View_采购订单    where 1=1 ";
            if (this.uiTextBox项目号开始.Text != "")
            {
                sql += " and csocode >= '" + this.uiTextBox项目号开始.Text.Trim() + "'";
            }
            if (this.uiTextBox项目号结束.Text != "")
            {
                sql += " and csocode <= '" + this.uiTextBox项目号结束.Text.Trim() + "'";
            }
            SqlHelper.SqlHelper.ExecuteNonQuery(sql);
            sql = "update " + tbname + " set 费用类型='全加工'    ";
            SqlHelper.SqlHelper.ExecuteNonQuery(sql);

            sql = "update " + tbname + " set TX='T0' , csocode=replace(csocode,'-T0','') where csocode like '%-T0%'  ";
            SqlHelper.SqlHelper.ExecuteNonQuery(sql);
            sql = "update " + tbname + " set TX='T1' , csocode=replace(csocode,'-T1','') where csocode like '%-T1%'  ";
            SqlHelper.SqlHelper.ExecuteNonQuery(sql);
            sql = "update " + tbname + " set TX='T2' , csocode=replace(csocode,'-T2','') where csocode like '%-T2%'  ";
            SqlHelper.SqlHelper.ExecuteNonQuery(sql);
            sql = "update " + tbname + " set TX='T3' , csocode=replace(csocode,'-T3','') where csocode like '%-T3%'  ";
            SqlHelper.SqlHelper.ExecuteNonQuery(sql);
            sql = "update " + tbname + " set TX='A' , csocode=replace(csocode,'-A','') where csocode like '%-A%'  ";
            SqlHelper.SqlHelper.ExecuteNonQuery(sql);
            sql = "update " + tbname + " set TX='B' , csocode=replace(csocode,'-B','') where csocode like '%-B%'  ";
            SqlHelper.SqlHelper.ExecuteNonQuery(sql);
            sql = "update " + tbname + " set 费用类型='原材料' where cInvCode like '%*'  ";
            SqlHelper.SqlHelper.ExecuteNonQuery(sql);
            sql = "update " + tbname + " set 费用类型='机械标准件' where len(cInvCode)=7 and 账套='002' ";
            SqlHelper.SqlHelper.ExecuteNonQuery(sql);

            sql = "update " + tbname + " set 费用类型='电气标准件' where cInvCode like '1%' and len(cInvCode)=5 and 账套 in ('003','009') ";
            SqlHelper.SqlHelper.ExecuteNonQuery(sql);

            sql = "update " + tbname + " set 费用类型='机械标准件' where cInvCode not like '1%' and len(cInvCode)=5 and 账套 in ('003','009')";
            SqlHelper.SqlHelper.ExecuteNonQuery(sql);
            //塞mes的
            sql = "insert into " + tbname + " (账套,类型,TX, dPODate, csocode, cInvCode, 费用类型,    ko_sch_amt,log_act_amt)";
            sql += " select 'MES','MES',tx,log_rec_date,job_code,part_code,hiyo_name,ko_sch_amt,log_act_amt from view_mes where 1=1  ";

            if (this.uiTextBox项目号开始.Text != "")
            {
                sql += " and job_code >= '" + this.uiTextBox项目号开始.Text.Trim() + "'";
            }
            if (this.uiTextBox项目号结束.Text != "")
            {
                sql += " and job_code <= '" + this.uiTextBox项目号结束.Text.Trim() + "'";
            }
            SqlHelper.SqlHelper.ExecuteNonQuery(sql);
            sql = "select  csocode 项目号,TX,类型, dPODate 日期,  cInvCode 物料号, 费用类型, cInvName 物料名称, cInvStd 规格型号,  CAST(ko_sch_amt AS DECIMAL(18, 2)) 计划金额," +
                "CAST(log_act_amt AS DECIMAL(18, 2)) 实际金额 from " + tbname;
            sql += " order by  csocode,tx ,类型,费用类型 ,cInvCode ";
            DataTable tb = new DataTable();
            //二次处理tb

            tb = SqlHelper.SqlHelper.ExecuteDataset(sql).Tables[0];
            this.gridControl1.DataSource = tb;
            table = tb;
            this.gridControl1.Refresh();

            sql = "drop table  " + tbname;
            SqlHelper.SqlHelper.ExecuteNonQuery(sql);
        }
        private void 汇总1查询()
        {
            string tbname = "[Temp" + Guid.NewGuid().ToString() + "]";

            string sql = @" SELECT     账套,'U8 ' 类型, dPODate, csocode, cInvCode,'88888888888888888888888888888888888' 费用类型,'T0' tx, cInvName, cInvStd, iQuantity, iUnitPrice, isum,isum ko_sch_amt,isum log_act_amt into " + tbname;
            sql += " FROM      View_采购订单    where 1=1 ";
            if (this.uiTextBox项目号开始.Text != "")
            {
                sql += " and csocode >= '" + this.uiTextBox项目号开始.Text.Trim() + "'";
            }
            if (this.uiTextBox项目号结束.Text != "")
            {
                sql += " and csocode <= '" + this.uiTextBox项目号结束.Text.Trim() + "'";
            }
            SqlHelper.SqlHelper.ExecuteNonQuery(sql);
            sql = "update " + tbname + " set 费用类型='全加工'    ";
            SqlHelper.SqlHelper.ExecuteNonQuery(sql);

            sql = "update " + tbname + " set TX='T0' , csocode=replace(csocode,'-T0','') where csocode like '%-T0%'  ";
            SqlHelper.SqlHelper.ExecuteNonQuery(sql);
            sql = "update " + tbname + " set TX='T1' , csocode=replace(csocode,'-T1','') where csocode like '%-T1%'  ";
            SqlHelper.SqlHelper.ExecuteNonQuery(sql);
            sql = "update " + tbname + " set TX='T2' , csocode=replace(csocode,'-T2','') where csocode like '%-T2%'  ";
            SqlHelper.SqlHelper.ExecuteNonQuery(sql);
            sql = "update " + tbname + " set TX='T3' , csocode=replace(csocode,'-T3','') where csocode like '%-T3%'  ";
            SqlHelper.SqlHelper.ExecuteNonQuery(sql);

            sql = "update " + tbname + " set TX='A' , csocode=replace(csocode,'-A','') where csocode like '%-A%'  ";
            SqlHelper.SqlHelper.ExecuteNonQuery(sql);
            sql = "update " + tbname + " set TX='B' , csocode=replace(csocode,'-B','') where csocode like '%-B%'  ";
            SqlHelper.SqlHelper.ExecuteNonQuery(sql);


            sql = "update " + tbname + " set 费用类型='原材料' where cInvCode like '%*'  ";
            SqlHelper.SqlHelper.ExecuteNonQuery(sql);
            sql = "update " + tbname + " set 费用类型='机械标准件' where len(cInvCode)=7 and 账套='002' ";
            SqlHelper.SqlHelper.ExecuteNonQuery(sql);

            sql = "update " + tbname + " set 费用类型='电气标准件' where cInvCode like '1%' and len(cInvCode)=5 and 账套 in ('003','009') ";
            SqlHelper.SqlHelper.ExecuteNonQuery(sql);

            sql = "update " + tbname + " set 费用类型='机械标准件' where cInvCode not like '1%' and len(cInvCode)=5 and 账套 in ('003','009')";
            SqlHelper.SqlHelper.ExecuteNonQuery(sql);
            //塞mes的
            sql = "insert into " + tbname + " (账套,类型,TX, dPODate, csocode, cInvCode, 费用类型,    ko_sch_amt,log_act_amt)";
            sql += " select 'MES','MES',tx,log_rec_date,job_code,part_code,hiyo_name,ko_sch_amt,log_act_amt from view_mes where 1=1  ";

            if (this.uiTextBox项目号开始.Text != "")
            {
                sql += " and job_code >= '" + this.uiTextBox项目号开始.Text.Trim() + "'";
            }
            if (this.uiTextBox项目号结束.Text != "")
            {
                sql += " and job_code <= '" + this.uiTextBox项目号结束.Text.Trim() + "'";
            }
            SqlHelper.SqlHelper.ExecuteNonQuery(sql);
            
           

                sql = @"	 select d.csocode 项目编号,CAST(d.计划总金额 AS DECIMAL(18, 2)) 计划总金额,CAST(d.实际总金额 AS DECIMAL(18, 2)) 实际总金额,
            a.TX,CAST(a.计划金额 AS DECIMAL(18, 2)) 计划金额,CAST(a.实际金额 AS DECIMAL(18, 2)) 实际金额,b.类型,CAST(b.计划金额 AS DECIMAL(18, 2)) 计划金额,CAST(b.实际金额 AS DECIMAL(18, 2)) 实际金额,
            c.费用类型,CAST(c.计划金额 AS DECIMAL(18, 2)) 计划金额,CAST(c.实际金额 AS DECIMAL(18, 2)) 实际金额
	 from 	 (select csocode ,  sum(ko_sch_amt) 计划总金额, sum(log_act_amt) 实际总金额 from " + tbname + " group by  csocode  ) d ";
                sql += @"  join 
                    (select csocode ,TX,  sum(ko_sch_amt)  计划金额, sum(log_act_amt) 实际金额 from " + tbname + " group by  csocode,TX ) a  on d.csocode=a.csocode";
                sql += @" join 
	 (select csocode,TX,类型, sum(ko_sch_amt) 计划金额, sum(log_act_amt) 实际金额 from  " + tbname + "  group by 类型,csocode,TX ) b on (d.csocode=b.csocode and a.csocode=b.csocode   and a.TX=b.TX)";
                sql += @" join 
	 (select csocode,TX,类型,费用类型, sum(ko_sch_amt) 计划金额, sum(log_act_amt) 实际金额 from  " + tbname + "  group by 类型,csocode,TX,费用类型) c";
                sql += @" on (d.csocode=c.csocode   and a.csocode=c.csocode and a.TX=c.TX and b.TX=C.TX  and b.csocode=c.csocode and b.类型=c.类型 )
	 order by  d.csocode asc ,a.TX,b.类型 desc,c.费用类型";
                DataTable tb = new DataTable();
                //二次处理tb

                tb = SqlHelper.SqlHelper.ExecuteDataset(sql).Tables[0];
                this.gridControl1.DataSource = tb;
                table = tb;


                sql = "drop table  " + tbname;
                SqlHelper.SqlHelper.ExecuteNonQuery(sql);
            
        }
        private void 汇总2查询()
        {
            string 项目类型where = " and 1=1 ";
            if (lookUpEdit项目类别.Text != "全部")
            {
                项目类型where = $"and 项目类别 = '{lookUpEdit项目类别.Text}'";
            }
            string tbname = "[Temp" + Guid.NewGuid().ToString() + "]";


            string sql = $@" SELECT     账套,'U8 ' 类型, dPODate, csocode,客户,存货名称,项目类别,价税合计,项目预算, cInvCode,'88888888888888888888888888888888888' 费用类型,'T0' tx,
                            cInvName, cInvStd, iQuantity, iUnitPrice, isum,isum ko_sch_amt,isum log_act_amt into " + tbname;
            sql += $" FROM      View_采购订单3    where 1=2";
  SqlHelper.SqlHelper.ExecuteNonQuery(sql);//建表


            //    sql = $@" SELECT     账套,'U8 ' 类型, dPODate, csocode,客户,存货名称,项目类别,价税合计,项目预算, cInvCode,'88888888888888888888888888888888888' 费用类型,'T0' tx,
            //                cInvName, cInvStd, iQuantity, iUnitPrice, isum,isum ko_sch_amt,isum log_act_amt into " + tbname;
            //sql += $" FROM      View_采购订单2    where 1=1 {项目类型where}";


            //if (this.uiTextBox项目号开始.Text != "")
            //{
            //    sql += " and csocode >= '" + this.uiTextBox项目号开始.Text.Trim() + "'";
            //}
            //if (this.uiTextBox项目号结束.Text != "")
            //{
            //    sql += " and csocode <= '" + this.uiTextBox项目号结束.Text.Trim() + "'";
            //}
            //SqlHelper.SqlHelper.ExecuteNonQuery(sql
            //
            查U8(tbname, 项目类型where, this.uiTextBox项目号开始.Text.Trim(), this.uiTextBox项目号结束.Text.Trim() ,dateEdit年月度.DateTime, dateEdit结束.DateTime);
            U8用销售更新采购2(tbname);

            sql = "update " + tbname + " set 费用类型='全加工'    ";
            SqlHelper.SqlHelper.ExecuteNonQuery(sql);

            sql = "update " + tbname + " set TX='T0' , csocode=replace(csocode,'-T0','') where csocode like '%-T0%'  ";
            SqlHelper.SqlHelper.ExecuteNonQuery(sql);
            sql = "update " + tbname + " set TX='T1' , csocode=replace(csocode,'-T1','') where csocode like '%-T1%'  ";
            SqlHelper.SqlHelper.ExecuteNonQuery(sql);
            sql = "update " + tbname + " set TX='T2' , csocode=replace(csocode,'-T2','') where csocode like '%-T2%'  ";
            SqlHelper.SqlHelper.ExecuteNonQuery(sql);
            sql = "update " + tbname + " set TX='T3' , csocode=replace(csocode,'-T3','') where csocode like '%-T3%'  ";
            SqlHelper.SqlHelper.ExecuteNonQuery(sql);
            sql = "update " + tbname + " set TX='A' , csocode=replace(csocode,'-A','') where csocode like '%-A%'  ";
            SqlHelper.SqlHelper.ExecuteNonQuery(sql);
            sql = "update " + tbname + " set TX='B' , csocode=replace(csocode,'-B','') where csocode like '%-B%'  ";
            SqlHelper.SqlHelper.ExecuteNonQuery(sql);

            sql = "update " + tbname + " set 费用类型='原材料' where cInvCode like '%*'  ";
            SqlHelper.SqlHelper.ExecuteNonQuery(sql);
            sql = "update " + tbname + " set 费用类型='机械标准件' where len(cInvCode)=7 and 账套='002' ";
            SqlHelper.SqlHelper.ExecuteNonQuery(sql);

            sql = "update " + tbname + " set 费用类型='电气标准件' where cInvCode like '1%' and len(cInvCode)=5 and 账套 in ('003','009') ";
            SqlHelper.SqlHelper.ExecuteNonQuery(sql);

            sql = "update " + tbname + " set 费用类型='机械标准件' where cInvCode not like '1%' and len(cInvCode)=5 and 账套 in ('003','009')";
            SqlHelper.SqlHelper.ExecuteNonQuery(sql);


            //塞mes的
            //sql = "insert into " + tbname + " (账套,类型,TX, dPODate, csocode,客户,存货名称,项目类别,价税合计,项目预算, cInvCode, 费用类型,    ko_sch_amt,log_act_amt)";
            //sql += $" select 'MES','MES',tx,log_rec_date,job_code,'' 客户,'' 存货名称,'' 项目类别,0 价税合计,0 项目预算,part_code,hiyo_name,ko_sch_amt,log_act_amt from view_mes where 1=1 ";

            //if (this.uiTextBox项目号开始.Text != "")
            //{
            //    sql += " and job_code >= '" + this.uiTextBox项目号开始.Text.Trim() + "'";
            //}
            //if (this.uiTextBox项目号结束.Text != "")
            //{
            //    sql += " and job_code <= '" + this.uiTextBox项目号结束.Text.Trim() + "'";
            //}
            //SqlHelper.SqlHelper.ExecuteNonQuery(sql);
            查MES(tbname, this.uiTextBox项目号开始.Text.Trim(), this.uiTextBox项目号结束.Text.Trim(), dateEdit年月度.DateTime, dateEdit结束.DateTime);








            //用U8更新mes
            //sql = $@"update {tbname} set 客户 = t.客户,项目类别 = t.项目类别,价税合计 = t.价税合计,项目预算 = t.项目预算,存货名称 = t.存货名称
            //            from {tbname},(select top 1 * from {tbname} where 类型 = 'U8' order by 价税合计 ,项目预算)t where {tbname}.csocode = t.csocode  --and {tbname}.类型 = 'MES'";
            //SqlHelper.SqlHelper.ExecuteNonQuery(sql);


            //sql = $@"
            //            ;WITH RankedData AS (
            //                SELECT 
            //                    *,
            //                    ROW_NUMBER() OVER (
            //                        PARTITION BY csocode 
            //                        ORDER BY 价税合计, 项目预算
            //                    ) AS RowNum
            //                FROM 
            //                    {tbname}
            //                WHERE 
            //                    类型 = 'U8'
            //            )
            //            UPDATE main
            //            SET 
            //                客户 = t.客户,
            //                项目类别 = t.项目类别,
            //                价税合计 = t.价税合计,
            //                项目预算 = t.项目预算,
            //                存货名称 = t.存货名称
            //            FROM 
            //               {tbname} AS main
            //            INNER JOIN 
            //                RankedData AS t
            //            ON 
            //                main.csocode = t.csocode
            //            WHERE 
            //                t.RowNum = 1;
            //            ";
            //       SqlHelper.SqlHelper.ExecuteNonQuery(sql);

            //用U8更新MES(tbname);
            U8用销售更新采购(tbname);

            //sql = $@"update {tbname} set cInvName = view_inventory.cInvName,存货名称 = view_inventory.cInvName
            //            from {tbname},view_inventory  where {tbname}.cInvCode = view_inventory.cInvCode  and {tbname}.类型 = 'MES'";
            //SqlHelper.SqlHelper.ExecuteNonQuery(sql);

            string dateWhere = " and 1=1 ";
            if (dateEdit年月度.EditValue != null)
            {

                dateWhere += $" and dPODate >= '{dateEdit年月度.DateTime.ToString("yyyy-MM-dd")}'";
            }

            if (dateEdit结束.EditValue != null)
            {
                dateWhere += $" and dPODate < '{dateEdit结束.DateTime.AddDays(1).ToString("yyyy-MM-dd")}'";
            }

            if (checkEdit人工费.Checked == true)
            {
                //从tb移除
                dateWhere += @" and 费用类型 <> '项目人工'";
            }
            if (checkEdit设计费.Checked == true)
            {
                //从tb移除
                dateWhere += @" and 费用类型 <> '设计费用'";
            }


            sql = $@"select c.客户,c.存货名称 存货名称,d.csocode 项目编号,c.项目类别,CAST(isnull(c.价税合计,0) AS DECIMAL(18, 2)) 价税合计,CAST(isnull(c.项目预算,0) AS DECIMAL(18, 2)) 项目预算,--d.计划总金额,
                    CAST(isnull(d.实际总金额,0) AS DECIMAL(18, 2)) 实际总金额,a.TX,CAST(isnull(a.计划金额,0) AS DECIMAL(18, 2)) 工艺成本,CAST(isnull(a.实际金额,0) AS DECIMAL(18, 2)) 实际金额,b.类型,CAST(isnull(b.计划金额,0) AS DECIMAL(18, 2)) 工艺成本1,
                CAST(isnull(b.实际金额,0) AS DECIMAL(18, 2)) 实际金额,case when isnull(a.实际金额,0) = 0 then 0 else ROUND((isnull(b.实际金额,0) / NULLIF(a.实际金额, 0)) , 4) end 占比1 ,
                    c.费用类型,CAST(isnull(c.计划金额,0) AS DECIMAL(18, 2)) 计划金额,CAST(isnull(c.实际金额,0) AS DECIMAL(18, 2)) 实际金额,case when isnull(a.实际金额,0) = 0 then 0 else ROUND((isnull(c.实际金额,0) / NULLIF(a.实际金额, 0)) , 4) end 占比2
	                 from 	 (
	                 select csocode,  sum(ko_sch_amt) 计划总金额, sum(log_act_amt) 实际总金额 from {tbname} where 1=1 {dateWhere} 
	                 group by  csocode  ) d   
	                 join 
                      (select csocode,TX,  sum(ko_sch_amt)  计划金额, sum(log_act_amt) 实际金额 
	                  from {tbname}  where 1=1 {dateWhere} 
	                  group by  csocode,TX ) a  on d.csocode=a.csocode 
	                  join 
	                 (select csocode,TX,类型, sum(ko_sch_amt) 计划金额, sum(log_act_amt) 实际金额 
	                 from  {tbname}   where 1=1 {dateWhere} 
	                 group by 类型,csocode,TX ) b on (d.csocode=b.csocode and a.csocode=b.csocode   and a.TX=b.TX) 
	                 join 
	                 (select csocode,存货名称,客户,项目类别,价税合计,项目预算,TX,类型,费用类型, sum(ko_sch_amt) 计划金额, sum(log_act_amt) 实际金额 
	                 from  {tbname}  where 1=1 {dateWhere} 
                    group by 类型,csocode,存货名称,客户,项目类别,价税合计,项目预算,TX,费用类型) c on (d.csocode=c.csocode   and a.csocode=c.csocode and a.TX=c.TX and b.TX=C.TX  and b.csocode=c.csocode and b.类型=c.类型 ) 
                    where 1=1  {项目类型where}";

                

            sql +=   "order by  d.csocode asc ,a.TX,b.类型 desc,c.费用类型";
            DataTable tb = new DataTable();
            tb = SqlHelper.SqlHelper.ExecuteDataset(sql).Tables[0];

            if (tb!= null && tb.Rows.Count >0)
            {
                // 按项目编号和 TX 分组
                var groupedData项目编号 = tb.AsEnumerable()
                                            .GroupBy(row => new
                                            {
                                                项目编号 = row.Field<string>("项目编号"),
                                                //TX = row.Field<string>("TX")
                                            });

                // 处理每组数据
                foreach (var group项目编号 in groupedData项目编号)
                {
                    var groupedDataTX = group项目编号.AsEnumerable()
                                                           .GroupBy(row => new
                                                           {

                                                               TX = row.Field<string>("TX")
                                                           });

                    foreach (var groupTX in groupedDataTX)
                    {
                        var groupedData类型 = groupTX.AsEnumerable()
                                                      .GroupBy(row => new
                                                      {
                                                          TX = row.Field<string>("类型")
                                                      });
                        decimal 合计占比2 = 0;
                        foreach (var grpup2 in groupTX.AsEnumerable())
                        {
                            合计占比2 += grpup2.Field<decimal>("占比2");
                        }
                        if (合计占比2 != 0)
                        {
                            decimal 最后占比 = 1 - 合计占比2;
                            //

                            groupTX.AsEnumerable().Last()["占比2"] = groupTX.AsEnumerable().Last().Field<decimal>("占比2") + 最后占比;
                            
                        }


                        decimal 合计 = 0;
                        foreach (var grpup in groupedData类型)
                        {
                            合计 += grpup.AsEnumerable().First().Field<decimal>("占比1");
                        }
                        if (合计 != 0)
                        {
                            decimal 最后占比 = 1 - 合计;
                            //
                            foreach (var item in groupedData类型.Last())
                            {
                                item["占比1"] = item.Field<decimal>("占比1") + 最后占比;
                            }
                        }
                       
                    }

                   
                }
            }



            this.gridControl1.DataSource = tb;
            table = tb;

            gridView1.Columns["占比1"].DisplayFormat.FormatType = DevExpress.Utils.FormatType.Numeric;
            gridView1.Columns["占比1"].DisplayFormat.FormatString = "P2";

            gridView1.Columns["占比2"].DisplayFormat.FormatType = DevExpress.Utils.FormatType.Numeric;
            gridView1.Columns["占比2"].DisplayFormat.FormatString = "P2";

            this.gridControl1.Refresh();
            
            sql = "drop table  " + tbname;
            SqlHelper.SqlHelper.ExecuteNonQuery(sql);
        }  
        
        private void 汇总3查询()
        {
            string 项目类型where = " and 1=1 ";
            if (lookUpEdit项目类别.Text != "全部")
            {
                项目类型where = $"and 项目类别 = '{lookUpEdit项目类别.Text}'";
            }
            string tbname = "[Temp" + Guid.NewGuid().ToString() + "]";

            string sql = $@" SELECT     账套,'U8 ' 类型, dPODate, csocode,客户,存货名称,项目类别,价税合计,项目预算, cInvCode,'88888888888888888888888888888888888' 费用类型,'T0' tx,
                            cInvName, cInvStd, iQuantity, iUnitPrice, isum,isum ko_sch_amt,isum log_act_amt,CAST(0 AS DECIMAL(10, 4)) miss_sj_amt into " + tbname;
            sql += $" FROM      View_采购订单3    where 1=2";
            SqlHelper.SqlHelper.ExecuteNonQuery(sql);//建表


            //string sql = $@" SELECT     账套,'U8 ' 类型, dPODate, csocode,客户,存货名称,项目类别,价税合计,项目预算, cInvCode,'88888888888888888888888888888888888' 费用类型,'T0' tx,
            //                cInvName, cInvStd, iQuantity, iUnitPrice, isum,isum ko_sch_amt,isum log_act_amt into " + tbname;
            //sql += $" FROM      View_采购订单2    where 1=1 {项目类型where}";
            //if (this.uiTextBox项目号开始.Text != "")
            //{
            //    sql += " and csocode >= '" + this.uiTextBox项目号开始.Text.Trim() + "'";
            //}
            //if (this.uiTextBox项目号结束.Text != "")
            //{
            //    sql += " and csocode <= '" + this.uiTextBox项目号结束.Text.Trim() + "'";
            //}

            //SqlHelper.SqlHelper.ExecuteNonQuery(sql);

            查U8(tbname, 项目类型where, this.uiTextBox项目号开始.Text.Trim(), this.uiTextBox项目号结束.Text.Trim(), dateEdit年月度.DateTime, dateEdit结束.DateTime);
            U8用销售更新采购2(tbname);
            sql = "update " + tbname + " set 费用类型='全加工'  ,miss_sj_amt = 0  ";
            SqlHelper.SqlHelper.ExecuteNonQuery(sql);

            sql = "update " + tbname + " set TX='T0' , csocode=replace(csocode,'-T0','') where csocode like '%-T0%';  ";
            SqlHelper.SqlHelper.ExecuteNonQuery(sql);
            sql = "update " + tbname + " set TX='T1' , csocode=replace(csocode,'-T1','') where csocode like '%-T1%';  ";
            SqlHelper.SqlHelper.ExecuteNonQuery(sql);
            sql = "update " + tbname + " set TX='T2' , csocode=replace(csocode,'-T2','') where csocode like '%-T2%';  ";
            SqlHelper.SqlHelper.ExecuteNonQuery(sql);
            sql = "update " + tbname + " set TX='T3' , csocode=replace(csocode,'-T3','') where csocode like '%-T3%';  ";
            SqlHelper.SqlHelper.ExecuteNonQuery(sql);
            sql = "update " + tbname + " set TX='A' , csocode=replace(csocode,'-A','') where csocode like '%-A%'  ";
            SqlHelper.SqlHelper.ExecuteNonQuery(sql);
            sql = "update " + tbname + " set TX='B' , csocode=replace(csocode,'-B','') where csocode like '%-B%'  ";
            SqlHelper.SqlHelper.ExecuteNonQuery(sql);

            sql = "update " + tbname + " set 费用类型='原材料' where cInvCode like '%*'  ";
            SqlHelper.SqlHelper.ExecuteNonQuery(sql);
            sql = "update " + tbname + " set 费用类型='机械标准件' where len(cInvCode)=7 and 账套='002' ";
            SqlHelper.SqlHelper.ExecuteNonQuery(sql);

            sql = "update " + tbname + " set 费用类型='电气标准件' where cInvCode like '1%' and len(cInvCode)=5 and 账套 in ('003','009') ";
            SqlHelper.SqlHelper.ExecuteNonQuery(sql);

            sql = "update " + tbname + " set 费用类型='机械标准件' where cInvCode not like '1%' and len(cInvCode)=5 and 账套 in ('003','009')";
            SqlHelper.SqlHelper.ExecuteNonQuery(sql);
            //塞mes的
            //sql = "insert into " + tbname + " (账套,类型,TX, dPODate, csocode,客户,存货名称,项目类别,价税合计,项目预算, cInvCode, 费用类型,    ko_sch_amt,log_act_amt)";
            //sql += $" select 'MES','MES',tx,log_rec_date,job_code,'' 客户,'' 存货名称,'' 项目类别,0 价税合计,0 项目预算,part_code,hiyo_name,ko_sch_amt,log_act_amt from view_mes where 1=1 ";

            //if (this.uiTextBox项目号开始.Text != "")
            //{
            //    sql += " and job_code >= '" + this.uiTextBox项目号开始.Text.Trim() + "'";
            //}
            //if (this.uiTextBox项目号结束.Text != "")
            //{
            //    sql += " and job_code <= '" + this.uiTextBox项目号结束.Text.Trim() + "'";
            //}
            //SqlHelper.SqlHelper.ExecuteNonQuery(sql);

            查MES减错误成本(tbname, this.uiTextBox项目号开始.Text.Trim(), this.uiTextBox项目号结束.Text.Trim(), dateEdit年月度.DateTime, dateEdit结束.DateTime);



            //用U8更新mes
            //sql = $@"update {tbname} set 客户 = t.客户,项目类别 = t.项目类别,价税合计 = t.价税合计,项目预算 = t.项目预算,存货名称 = t.存货名称
            //            from {tbname},(select top 1 * from {tbname} where 类型 = 'U8' order by 价税合计 ,项目预算)t where {tbname}.csocode = t.csocode  --and {tbname}.类型 = 'MES'";

            //sql = $@"
            //            ;WITH RankedData AS (
            //                SELECT 
            //                    *,
            //                    ROW_NUMBER() OVER (
            //                        PARTITION BY csocode 
            //                        ORDER BY 价税合计, 项目预算
            //                    ) AS RowNum
            //                FROM 
            //                    {tbname}
            //                WHERE 
            //                    类型 = 'U8'
            //            )
            //            UPDATE main
            //            SET 
            //                客户 = t.客户,
            //                项目类别 = t.项目类别,
            //                价税合计 = t.价税合计,
            //                项目预算 = t.项目预算,
            //                存货名称 = t.存货名称
            //            FROM 
            //               {tbname} AS main
            //            INNER JOIN 
            //                RankedData AS t
            //            ON 
            //                main.csocode = t.csocode
            //            WHERE 
            //                t.RowNum = 1;
            //            ";

            //SqlHelper.SqlHelper.ExecuteNonQuery(sql);

            //用U8更新MES(tbname);
            U8用销售更新采购(tbname);

            //sql = $@"update {tbname} set cInvName = view_inventory.cInvName,存货名称 = view_inventory.cInvName
            //            from {tbname},view_inventory  where {tbname}.cInvCode = view_inventory.cInvCode  and {tbname}.类型 = 'MES'";
            //SqlHelper.SqlHelper.ExecuteNonQuery(sql);

            string dateWhere = " and 1=1 ";
            if (dateEdit年月度.EditValue != null)
            {

                dateWhere += $" and dPODate >= '{dateEdit年月度.DateTime.ToString("yyyy-MM-dd")}'";
            }

            if (dateEdit结束.EditValue != null)
            {
                dateWhere += $" and dPODate < '{dateEdit结束.DateTime.AddDays(1).ToString("yyyy-MM-dd")}'";
            }

            if (checkEdit人工费.Checked == true)
            {
                //从tb移除
                dateWhere += @" and 费用类型 <> '项目人工'";
            }
            if (checkEdit设计费.Checked == true)
            {
                //从tb移除
                dateWhere += @" and 费用类型 <> '设计费用'";
            }
            DataTable dtTXAB = SqlHelper.SqlHelper.ExecuteDataset($"SELECT 1 FROM {tbname} where TX in ('A','B')").Tables[0];
            if (dtTXAB != null && dtTXAB.Rows.Count > 0)
            {
                sql = $@"WITH PivotType AS (
                    -- 第一轮 PIVOT：转换 类型 (MES, U8)
                    SELECT 
                        csocode AS 项目编号,
                        SUM(CASE WHEN 类型 = 'MES' THEN log_act_amt ELSE 0 END) AS MES,
                        SUM(CASE WHEN 类型 = 'U8' THEN log_act_amt ELSE 0 END) AS U8
                    FROM {tbname}  where 1=1 {dateWhere}  {项目类型where}
                    GROUP BY csocode
                ),
                PivotTx AS (
                    -- 第二轮 PIVOT：转换 tx (T0, T1, T2, T3)
                    SELECT 
                        csocode AS 项目编号,
                        SUM(CASE WHEN tx = 'T0' THEN log_act_amt ELSE 0 END) AS T0,
                        SUM(CASE WHEN tx = 'T1' THEN log_act_amt ELSE 0 END) AS T1,
                        SUM(CASE WHEN tx = 'T2' THEN log_act_amt ELSE 0 END) AS T2,
                        SUM(CASE WHEN tx = 'T3' THEN log_act_amt ELSE 0 END) AS T3,
                        SUM(CASE WHEN tx = 'A' THEN log_act_amt ELSE 0 END) AS A,
                        SUM(CASE WHEN tx = 'B' THEN log_act_amt ELSE 0 END) AS B
                    FROM {tbname}  where 1=1 {dateWhere}  {项目类型where}
                    GROUP BY csocode
                )
                -- 合并两次 PIVOT 的结果，并计算 预算差额 和 占比
                SELECT 
                    t.客户,
                    t.存货名称,
                    t.csocode AS 项目编号,
                    t.项目类别,
                    CAST(MAX(t.价税合计)AS DECIMAL(18, 2)) AS 价税合计, 
                    CAST(MAX(t.项目预算)AS DECIMAL(18, 2)) AS 项目预算,
                    CAST(SUM(t.log_act_amt)AS DECIMAL(18, 2)) AS 实际总成本,
                    CAST(SUM(t.log_act_amt)  /1.13 AS DECIMAL(18, 2)) AS 未税总成本,
                    -- 计算 预算差额 = 项目预算 - 实际总成本
                    CAST(MAX(t.项目预算) - SUM(t.log_act_amt)AS DECIMAL(18, 2)) AS 预算差额,
                    -- 计算 占比 = (价税合计 - 实际总成本) / 价税合计
                    CASE 
                        WHEN MAX(t.价税合计) = 0 THEN NULL  -- 避免除零错误
                        ELSE (MAX(t.价税合计) - SUM(t.log_act_amt)) / MAX(t.价税合计)
                    END AS 毛利占比,
                    CAST(ISNULL(pt.MES, 0)AS DECIMAL(18, 2)) AS MES,
                    CAST(ISNULL(pt.U8, 0)AS DECIMAL(18, 2)) AS U8,
                    CAST(ISNULL(pxt.T0, 0)AS DECIMAL(18, 2)) AS T0,
                    CAST(ISNULL(pxt.T1, 0)AS DECIMAL(18, 2)) AS T1,
                    CAST(ISNULL(pxt.T2, 0)AS DECIMAL(18, 2)) AS T2,
                    CAST(ISNULL(pxt.T3, 0)AS DECIMAL(18, 2)) AS T3,
                     CAST(ISNULL(pxt.A, 0)AS DECIMAL(18, 2)) AS A,
                     CAST(ISNULL(pxt.B, 0)AS DECIMAL(18, 2)) AS B
                FROM {tbname} t  
                LEFT JOIN PivotType pt ON t.csocode = pt.项目编号
                LEFT JOIN PivotTx pxt ON t.csocode = pxt.项目编号 where 1=1 {dateWhere} {项目类型where}
                GROUP BY t.客户, t.存货名称, t.csocode, t.项目类别, pt.MES, pt.U8, pxt.T0, pxt.T1, pxt.T2, pxt.T3, pxt.A, pxt.B
                ";



                sql += "order by  项目编号";
            }
            else
            {


                sql = $@"WITH PivotType AS (
                    -- 第一轮 PIVOT：转换 类型 (MES, U8)
                    SELECT 
                        csocode AS 项目编号,
                        SUM(CASE WHEN 类型 = 'MES' THEN log_act_amt ELSE 0 END) AS MES,
                        SUM(CASE WHEN 类型 = 'U8' THEN log_act_amt ELSE 0 END) AS U8
                    FROM {tbname}  where 1=1 {dateWhere}  {项目类型where}
                    GROUP BY csocode
                ),
                PivotTx AS (
                    -- 第二轮 PIVOT：转换 tx (T0, T1, T2, T3)
                    SELECT 
                        csocode AS 项目编号,
                        SUM(CASE WHEN tx = 'T0' THEN log_act_amt ELSE 0 END) AS T0,
                        SUM(CASE WHEN tx = 'T1' THEN log_act_amt ELSE 0 END) AS T1,
                        SUM(CASE WHEN tx = 'T2' THEN log_act_amt ELSE 0 END) AS T2,
                        SUM(CASE WHEN tx = 'T3' THEN log_act_amt ELSE 0 END) AS T3
                    FROM {tbname}  where 1=1 {dateWhere}  {项目类型where}
                    GROUP BY csocode
                )
                -- 合并两次  PIVOT 的结果，并计算 预算差额 和 占比
                SELECT 
                    t.客户,
                    t.存货名称,
                    t.csocode AS 项目编号,
                    t.项目类别,
                    CAST(MAX(t.价税合计)AS DECIMAL(18, 2)) AS 价税合计, 
                    CAST(MAX(t.项目预算)AS DECIMAL(18, 2)) AS 项目预算,
                    CAST(SUM(t.log_act_amt)AS DECIMAL(18, 2)) AS 实际总成本,
                     CAST(SUM(t.log_act_amt)  /1.13 AS DECIMAL(18, 2)) AS 未税总成本,
                    -- 计算 预算差额 = 项目预算 - 实际总成本
                    CAST(MAX(t.项目预算) - SUM(t.log_act_amt)AS DECIMAL(18, 2)) AS 预算差额,
                    -- 计算 占比 = (价税合计 - 实际总成本) / 价税合计
                    CASE 
                        WHEN MAX(t.价税合计) = 0 THEN NULL  -- 避免除零错误
                        ELSE (MAX(t.价税合计) - SUM(t.log_act_amt)) / MAX(t.价税合计)
                    END AS 毛利占比,
                    CAST(ISNULL(pt.MES, 0)AS DECIMAL(18, 2)) AS MES,
                    CAST(ISNULL(pt.U8, 0)AS DECIMAL(18, 2)) AS U8,
                    CAST(ISNULL(pxt.T0, 0)AS DECIMAL(18, 2)) AS T0,
                    CAST(ISNULL(pxt.T1, 0)AS DECIMAL(18, 2)) AS T1,
                    CAST(ISNULL(pxt.T2, 0)AS DECIMAL(18, 2)) AS T2,
                    CAST(ISNULL(pxt.T3, 0)AS DECIMAL(18, 2)) AS T3
                FROM {tbname} t  
                LEFT JOIN PivotType pt ON t.csocode = pt.项目编号
                LEFT JOIN PivotTx pxt ON t.csocode = pxt.项目编号 where 1=1 {dateWhere} {项目类型where}
                GROUP BY t.客户, t.存货名称, t.csocode, t.项目类别, pt.MES, pt.U8, pxt.T0, pxt.T1, pxt.T2, pxt.T3
                ";



                sql += "order by  项目编号";
            }
            //减去错误成本
            if (dtTXAB != null && dtTXAB.Rows.Count > 0)
            {
                sql = $@"WITH PivotType AS (
                    -- 第一轮 PIVOT：转换 类型 (MES, U8)
                    SELECT 
                        csocode AS 项目编号,
                        SUM(CASE WHEN 类型 = 'MES' THEN log_act_amt ELSE 0 END) AS MES,
                        SUM(CASE WHEN 类型 = 'U8' THEN log_act_amt ELSE 0 END) AS U8
                    FROM {tbname}  where 1=1 {dateWhere}  {项目类型where}
                    GROUP BY csocode
                ),
                PivotTx AS (
                    -- 第二轮 PIVOT：转换 tx (T0, T1, T2, T3)
                    SELECT 
                        csocode AS 项目编号,
                        SUM(CASE WHEN tx = 'T0' THEN log_act_amt-miss_sj_amt ELSE 0 END) AS T0,
                       SUM(CASE WHEN tx = 'T1' THEN log_act_amt ELSE 0 END) + 
        SUM(CASE WHEN tx IN ('T0', 'T1', 'T2', 'T3') THEN miss_sj_amt ELSE 0 END) AS T1,
                        SUM(CASE WHEN tx = 'T2' THEN log_act_amt-miss_sj_amt ELSE 0 END) AS T2,
                        SUM(CASE WHEN tx = 'T3' THEN log_act_amt-miss_sj_amt ELSE 0 END) AS T3,
                        SUM(CASE WHEN tx = 'A' THEN log_act_amt ELSE 0 END) AS A,
                        SUM(CASE WHEN tx = 'B' THEN log_act_amt ELSE 0 END) AS B
                    FROM {tbname}  where 1=1 {dateWhere}  {项目类型where}
                    GROUP BY csocode
                )
                -- 合并两次 PIVOT 的结果，并计算 预算差额 和 占比
                SELECT 
                    t.客户,
                    t.存货名称,
                    t.csocode AS 项目编号,
                    t.项目类别,
                    CAST(MAX(t.价税合计)AS DECIMAL(18, 2)) AS 价税合计, 
                    CAST(MAX(t.项目预算)AS DECIMAL(18, 2)) AS 项目预算,
                    CAST(SUM(t.log_act_amt)AS DECIMAL(18, 2)) AS 实际总成本,
                    CAST(SUM(t.log_act_amt)  /1.13 AS DECIMAL(18, 2)) AS 未税总成本,
                    -- 计算 预算差额 = 项目预算 - 实际总成本
                    CAST(MAX(t.项目预算) - SUM(t.log_act_amt)AS DECIMAL(18, 2)) AS 预算差额,
                    -- 计算 占比 = (价税合计 - 实际总成本) / 价税合计
                    CASE 
                        WHEN MAX(t.价税合计) = 0 THEN NULL  -- 避免除零错误
                        ELSE (MAX(t.价税合计) - SUM(t.log_act_amt)) / MAX(t.价税合计)
                    END AS 毛利占比,
                    CAST(ISNULL(pt.MES, 0)AS DECIMAL(18, 2)) AS MES,
                    CAST(ISNULL(pt.U8, 0)AS DECIMAL(18, 2)) AS U8,
                    CAST(ISNULL(pxt.T0, 0)AS DECIMAL(18, 2)) AS T0,
                    CAST(ISNULL(pxt.T1, 0)AS DECIMAL(18, 2)) AS T1,
                    CAST(ISNULL(pxt.T2, 0)AS DECIMAL(18, 2)) AS T2,
                    CAST(ISNULL(pxt.T3, 0)AS DECIMAL(18, 2)) AS T3,
                     CAST(ISNULL(pxt.A, 0)AS DECIMAL(18, 2)) AS A,
                     CAST(ISNULL(pxt.B, 0)AS DECIMAL(18, 2)) AS B
                FROM {tbname} t  
                LEFT JOIN PivotType pt ON t.csocode = pt.项目编号
                LEFT JOIN PivotTx pxt ON t.csocode = pxt.项目编号 where 1=1 {dateWhere} {项目类型where}
                GROUP BY t.客户, t.存货名称, t.csocode, t.项目类别, pt.MES, pt.U8, pxt.T0, pxt.T1, pxt.T2, pxt.T3, pxt.A, pxt.B
                ";



                sql += "order by  项目编号";
            }
            else
            {


                sql = $@"WITH PivotType AS (
                    -- 第一轮 PIVOT：转换 类型 (MES, U8)
                    SELECT 
                        csocode AS 项目编号,
                        SUM(CASE WHEN 类型 = 'MES' THEN log_act_amt ELSE 0 END) AS MES,
                        SUM(CASE WHEN 类型 = 'U8' THEN log_act_amt ELSE 0 END) AS U8
                    FROM {tbname}  where 1=1 {dateWhere}  {项目类型where}
                    GROUP BY csocode
                ),
                PivotTx AS (
                    -- 第二轮 PIVOT：转换 tx (T0, T1, T2, T3)
                    SELECT 
                        csocode AS 项目编号,
                          SUM(CASE WHEN tx = 'T0' THEN log_act_amt-miss_sj_amt ELSE 0 END) AS T0,
                        SUM(CASE WHEN tx = 'T1' THEN log_act_amt ELSE 0 END) + 
        SUM(CASE WHEN tx IN ('T0', 'T1', 'T2', 'T3') THEN miss_sj_amt ELSE 0 END) AS T1,
                        SUM(CASE WHEN tx = 'T2' THEN log_act_amt-miss_sj_amt ELSE 0 END) AS T2,
                        SUM(CASE WHEN tx = 'T3' THEN log_act_amt-miss_sj_amt ELSE 0 END) AS T3
                    FROM {tbname}  where 1=1 {dateWhere}  {项目类型where}
                    GROUP BY csocode
                )
                -- 合并两次  PIVOT 的结果，并计算 预算差额 和 占比
                SELECT 
                    t.客户,
                    t.存货名称,
                    t.csocode AS 项目编号,
                    t.项目类别,
                    CAST(MAX(t.价税合计)AS DECIMAL(18, 2)) AS 价税合计, 
                    CAST(MAX(t.项目预算)AS DECIMAL(18, 2)) AS 项目预算,
                    CAST(SUM(t.log_act_amt)AS DECIMAL(18, 2)) AS 实际总成本,
                    CAST(SUM(t.log_act_amt)  /1.13 AS DECIMAL(18, 2)) AS 未税总成本,
                    -- 计算 预算差额 = 项目预算 - 实际总成本
                    CAST(MAX(t.项目预算) - SUM(t.log_act_amt)AS DECIMAL(18, 2)) AS 预算差额,
                    -- 计算 占比 = (价税合计 - 实际总成本) / 价税合计
                    CASE 
                        WHEN MAX(t.价税合计) = 0 THEN NULL  -- 避免除零错误
                        ELSE (MAX(t.价税合计) - SUM(t.log_act_amt)) / MAX(t.价税合计)
                    END AS 毛利占比,
                    CAST(ISNULL(pt.MES, 0)AS DECIMAL(18, 2)) AS MES,
                    CAST(ISNULL(pt.U8, 0)AS DECIMAL(18, 2)) AS U8,
                    CAST(ISNULL(pxt.T0, 0)AS DECIMAL(18, 2)) AS T0,
                    CAST(ISNULL(pxt.T1, 0)AS DECIMAL(18, 2)) AS T1,
                    CAST(ISNULL(pxt.T2, 0)AS DECIMAL(18, 2)) AS T2,
                    CAST(ISNULL(pxt.T3, 0)AS DECIMAL(18, 2)) AS T3
                FROM {tbname} t  
                LEFT JOIN PivotType pt ON t.csocode = pt.项目编号
                LEFT JOIN PivotTx pxt ON t.csocode = pxt.项目编号 where 1=1 {dateWhere} {项目类型where}
                GROUP BY t.客户, t.存货名称, t.csocode, t.项目类别, pt.MES, pt.U8, pxt.T0, pxt.T1, pxt.T2, pxt.T3
                ";



                sql += "order by  项目编号";
            }

            DataTable tb = new DataTable();
            tb = SqlHelper.SqlHelper.ExecuteDataset(sql).Tables[0];



            this.gridControl1.DataSource = tb;
            table = tb;

            gridView1.Columns["毛利占比"].DisplayFormat.FormatType = DevExpress.Utils.FormatType.Numeric;
            gridView1.Columns["毛利占比"].DisplayFormat.FormatString = "P2";

            gridView1.Columns["价税合计"].SummaryItem.SummaryType = DevExpress.Data.SummaryItemType.Sum;
            gridView1.Columns["价税合计"].SummaryItem.DisplayFormat = "{0}";

            gridView1.Columns["项目预算"].SummaryItem.SummaryType = DevExpress.Data.SummaryItemType.Sum;
            gridView1.Columns["项目预算"].SummaryItem.DisplayFormat = "{0}";

            gridView1.Columns["实际总成本"].SummaryItem.SummaryType = DevExpress.Data.SummaryItemType.Sum;
            gridView1.Columns["实际总成本"].SummaryItem.DisplayFormat = "{0}";

            gridView1.Columns["MES"].SummaryItem.SummaryType = DevExpress.Data.SummaryItemType.Sum;
            gridView1.Columns["MES"].SummaryItem.DisplayFormat = "{0}";

            gridView1.Columns["U8"].SummaryItem.SummaryType = DevExpress.Data.SummaryItemType.Sum;
            gridView1.Columns["U8"].SummaryItem.DisplayFormat = "{0}";

            gridView1.Columns["T0"].SummaryItem.SummaryType = DevExpress.Data.SummaryItemType.Sum;
            gridView1.Columns["T0"].SummaryItem.DisplayFormat = "{0}";

            gridView1.Columns["T1"].SummaryItem.SummaryType = DevExpress.Data.SummaryItemType.Sum;
            gridView1.Columns["T1"].SummaryItem.DisplayFormat = "{0}";

            gridView1.Columns["T2"].SummaryItem.SummaryType = DevExpress.Data.SummaryItemType.Sum;
            gridView1.Columns["T2"].SummaryItem.DisplayFormat = "{0}";

            gridView1.Columns["T3"].SummaryItem.SummaryType = DevExpress.Data.SummaryItemType.Sum;
            gridView1.Columns["T3"].SummaryItem.DisplayFormat = "{0}";

            this.gridControl1.Refresh();
            
            sql = "drop table  " + tbname;
            SqlHelper.SqlHelper.ExecuteNonQuery(sql);
        }


        private void 查U8(string tbname,string 项目类别, string 项目号开始, string 项目号结束, DateTime 开始时间, DateTime 结束时间)
        {
            //if (string.IsNullOrEmpty(项目号开始.Trim()) == false)
            //{
            //    项目号开始 = 处理项目号(项目号开始);
            //}

            //if (string.IsNullOrEmpty(项目号结束.Trim()) == false)
            //{
            //    项目号结束 = 处理项目号(项目号结束);
            //}


            string where = "and 1=1";
            if (string.IsNullOrWhiteSpace(项目号开始) == false)
            {
                //where += $@" and CASE 
                //            WHEN CHARINDEX('-', csocode) > 0 
                //            THEN LEFT(csocode, CHARINDEX('-', csocode) - 1)
                //            ELSE csocode
                //        END >= '{项目号开始}'";
                where += $@" and csocode >= '{项目号开始}'";
            }
            if (string.IsNullOrWhiteSpace(项目号结束) == false)
            {
                //where += $@" and CASE 
                //            WHEN CHARINDEX('-', csocode) > 0 
                //            THEN LEFT(csocode, CHARINDEX('-', csocode) - 1)
                //            ELSE csocode
                //        END <= '{项目号结束}'";

                where += $@"and csocode <= '{项目号结束}'";
            }
            //if (项目号开始.Trim() == "" && 项目号结束.Trim() == "")
            //{
            //    MessageBox.Show("查询项目不能都为空", "提示", MessageBoxButtons.OK);
            //    return;

            //}
            //if (项目号开始.Trim() != "" && 项目号结束.Trim() == "")
            //{

            //    where += $@" and csocode = '{项目号开始}'";


            //}
            //else if (项目号开始.Trim() == "" && 项目号结束.Trim() != "")
            //{
            //    where += $@" and csocode = '{项目号结束}'";
            //}
            //else
            //{
            //    where += $@" and csocode >= '{项目号开始}' and csocode <= '{项目号结束}'";
            //}

            if (开始时间 != DateTime.MinValue)
            {

                where += $" and dPODate >= '{开始时间.ToString("yyyy-MM-dd")}'";
            }

            if (结束时间 != DateTime.MinValue)
            {
                where += $" and dPODate < '{结束时间.AddDays(1).ToString("yyyy-MM-dd")}'";
            }
            //if (开始时间 == DateTime.MinValue && 结束时间 == DateTime.MinValue)
            //{
            //    // 塞mes的(第一年）
            //    for (int y = 2013; y <= DateTime.Now.Year; y++)
            //    {
            //        string sql = "";
            //        sql = $"insert into {tbname} ( 账套, 类型, dPODate, csocode, 客户,存货名称, 项目类别, 价税合计, 项目预算, cInvCode, 费用类型, tx, cInvName, cInvStd,iQuantity, iUnitPrice, isum, ko_sch_amt, log_act_amt)";
            //        sql += $@" SELECT     账套,'U8 ' 类型, dPODate, csocode,客户,存货名称,项目类别,价税合计,项目预算, cInvCode,'88888888888888888888888888888888888' 费用类型,'T0' tx,
            //                cInvName, cInvStd, iQuantity, iUnitPrice, isum,isum ko_sch_amt,isum log_act_amt FROM      View_采购订单3    where 1=1 {where}";
            //        sql += " and  year( dPODate)='" + y + "'";
            //        SqlHelper.SqlHelper.ExecuteNonQuery(sql);

            //    }
            //}
            //else
            //{
                string sql = "";
                sql = $"insert into {tbname} ( 账套, 类型, dPODate, csocode, 客户,存货名称, 项目类别, 价税合计, 项目预算, cInvCode, 费用类型, tx, cInvName, cInvStd,iQuantity, iUnitPrice, isum, ko_sch_amt, log_act_amt)";
                sql += $@" SELECT     账套,'U8 ' 类型, dPODate, csocode,客户,存货名称,项目类别,价税合计,项目预算, cInvCode,'88888888888888888888888888888888888' 费用类型,'T0' tx,
                            cInvName, cInvStd, iQuantity, iUnitPrice, isum,isum ko_sch_amt,isum log_act_amt FROM      View_采购订单3    where 1=1 {where}";
                SqlHelper.SqlHelper.ExecuteNonQuery(sql);

            //}
               
        }

        private string 处理项目号(string 项目号开始)
        {
            //1,有没有-
            if (项目号开始.IndexOf("-")>0)
            {
                //找第一个-后的字符是数字还是字母
                string s1=项目号开始.Substring(项目号开始.IndexOf("-")+1, 1);
                string s2= 项目号开始.Substring(0,项目号开始.IndexOf("-"));
                if (是否数字(s1)>0 )
                {
                    return 项目号开始;
                }
                else
                {
                    return s2;
                }

            }
            else
            {
                return 项目号开始;
            }
        }

        private int 是否数字(string s1)
        {
            try
            {
                return int.Parse(s1);
            }
            catch
            (Exception e)
            {
                return -1;
            }
        }

        private void 查MES(string tbname,string 项目号开始,string 项目号结束,DateTime 开始时间,DateTime 结束时间) {
            string where = "and 1=1";
            if (string.IsNullOrWhiteSpace(项目号开始) == false)
            {
                where += $" and job_code >= '{项目号开始}'";
            }
            if (string.IsNullOrWhiteSpace(项目号结束) == false)
            {
                where += $" and job_code <= '{项目号结束}'";
            }

            if (开始时间 != DateTime.MinValue)
            {

                where += $" and log_rec_date >= '{开始时间.ToString("yyyy-MM-dd")}'";
            }

            if (结束时间 != DateTime.MinValue)
            {
                where += $" and log_rec_date < '{结束时间.AddDays(1).ToString("yyyy-MM-dd")}'";
            }


            if (开始时间 == DateTime.MinValue && 结束时间 == DateTime.MinValue)
            {
               
                for (int y = 2010; y <= DateTime.Now.Year; y++)
                {
                    string sql = "";
                    sql = "insert into " + tbname + " (账套,类型,TX, dPODate, csocode,客户,存货名称,项目类别,价税合计,项目预算, cInvCode, 费用类型,    ko_sch_amt,log_act_amt)";
                    sql += $" select 'MES','MES',tx,log_rec_date,job_code,'' 客户,'' 存货名称,'' 项目类别,0 价税合计,0 项目预算,part_code,hiyo_name,ko_sch_amt,log_act_amt from view_mes where 1=1 ";
                    sql += " and  year( log_rec_date)='" + y + "'";
                    sql += where;
                    SqlHelper.SqlHelper.ExecuteNonQuery(sql);
                }
            }
            else
            {
                string sql = "";
                sql = "insert into " + tbname + " (账套,类型,TX, dPODate, csocode,客户,存货名称,项目类别,价税合计,项目预算, cInvCode, 费用类型,    ko_sch_amt,log_act_amt)";
                sql += $" select 'MES','MES',tx,log_rec_date,job_code,'' 客户,'' 存货名称,'' 项目类别,0 价税合计,0 项目预算,part_code,hiyo_name,ko_sch_amt,log_act_amt from view_mes where 1=1 ";
             
                sql += where;
                SqlHelper.SqlHelper.ExecuteNonQuery(sql);
            }
        }

        private void 查MES减错误成本(string tbname, string 项目号开始, string 项目号结束, DateTime 开始时间, DateTime 结束时间)
        {
            string where = "and 1=1";
            if (string.IsNullOrWhiteSpace(项目号开始) == false)
            {
                where += $" and job_code >= '{项目号开始}'";
            }
            if (string.IsNullOrWhiteSpace(项目号结束) == false)
            {
                where += $" and job_code <= '{项目号结束}'";
            }

            if (开始时间 != DateTime.MinValue)
            {

                where += $" and log_rec_date >= '{开始时间.ToString("yyyy-MM-dd")}'";
            }

            if (结束时间 != DateTime.MinValue)
            {
                where += $" and log_rec_date < '{结束时间.AddDays(1).ToString("yyyy-MM-dd")}'";
            }


            if (开始时间 == DateTime.MinValue && 结束时间 == DateTime.MinValue)
            {

                for (int y = 2010; y <= DateTime.Now.Year; y++)
                {
                    string sql = "";
                    sql = "insert into " + tbname + " (账套,类型,TX, dPODate, csocode,客户,存货名称,项目类别,价税合计,项目预算, cInvCode, 费用类型,    ko_sch_amt,log_act_amt,miss_sj_amt)";
                    sql += $" select 'MES','MES',tx,log_rec_date,job_code,'' 客户,'' 存货名称,'' 项目类别,0 价税合计,0 项目预算,part_code,hiyo_name,ko_sch_amt,log_act_amt, miss_sj_amt from view_mes where 1=1 ";
                    sql += " and  year( log_rec_date)='" + y + "'";
                    sql += where;
                    SqlHelper.SqlHelper.ExecuteNonQuery(sql);
                }
            }
            else
            {
                string sql = "";
                sql = "insert into " + tbname + " (账套,类型,TX, dPODate, csocode,客户,存货名称,项目类别,价税合计,项目预算, cInvCode, 费用类型,    ko_sch_amt,log_act_amt,miss_sj_amt)";
                sql += $" select 'MES','MES',tx,log_rec_date,job_code,'' 客户,'' 存货名称,'' 项目类别,0 价税合计,0 项目预算,part_code,hiyo_name,ko_sch_amt,log_act_amt , miss_sj_amt from view_mes where 1=1 ";

                sql += where;
                SqlHelper.SqlHelper.ExecuteNonQuery(sql);
            }
        }
        private void 用U8更新MES(string tbname)
        {
            string sql = $@"
                        ;WITH RankedData AS (
                            SELECT 
                                *,
                                ROW_NUMBER() OVER (
                                    PARTITION BY csocode 
                                    ORDER BY 价税合计 desc, 项目预算 desc
                                ) AS RowNum
                            FROM 
                                {tbname}
                            WHERE 
                                类型 = 'U8'
                        )
                        UPDATE main
                        SET 
                            客户 = t.客户,
                            项目类别 = t.项目类别,
                            价税合计 = t.价税合计,
                            项目预算 = t.项目预算,
                            存货名称 = t.存货名称
                        FROM 
                           {tbname} AS main
                        INNER JOIN 
                            RankedData AS t
                        ON 
                            main.csocode = t.csocode
                        WHERE 
                            t.RowNum = 1;
                        ";

            SqlHelper.SqlHelper.ExecuteNonQuery(sql);
        }

        private void U8用销售更新采购(string tbname)
        {
            string sql条数 = $"select count(1) from {tbname}";

           

            int 条数 = (int)SqlHelper.SqlHelper.ExecuteScalar(sql条数);
            if (条数 > 0)
            {
                string sql = $@"update {tbname} set 客户 =t.客户 ,	存货名称 =t.存货名称 ,	项目类别 =t.项目类别 ,	价税合计 =t.价税合计 ,	项目预算 =t.项目预算 
                            from {tbname} join  (SELECT 
                                *,
                                ROW_NUMBER() OVER (
                                    PARTITION BY csocode 
                                    ORDER BY 价税合计 desc, 项目预算 desc
                                ) AS RowNum
                            FROM View_销售订单2)t on {tbname}.cSOCode = t.cSOCode";
                SqlHelper.SqlHelper.ExecuteNonQuery(sql);
            }
            else
            {
                string where = "and 1=1";

                //if (项目号开始.Trim() == "" && 项目号结束.Trim() == "")
                //{
                //    MessageBox.Show("查询项目不能都为空", "提示", MessageBoxButtons.OK);
                //    return;

                //}
                //if (项目号开始.Trim() != "" && 项目号结束.Trim() == "")
                //{

                //    where += $@" and csocode = '{项目号开始}'";


                //}
                //else if (项目号开始.Trim() == "" && 项目号结束.Trim() != "")
                //{
                //    where += $@" and csocode = '{项目号结束}'";
                //}
                //else
                //{
                //    where += $@" and csocode >= '{项目号结束}' and csocode <= '{项目号结束}'";
                //}

                if (string.IsNullOrWhiteSpace(uiTextBox项目号开始.Text.Trim()) == false)
                {
                    where += $" and csocode >= '{uiTextBox项目号开始.Text.Trim()}'";
                }
                if (string.IsNullOrWhiteSpace(uiTextBox项目号结束.Text.Trim()) == false)
                {
                    where += $" and csocode <= '{uiTextBox项目号结束.Text.Trim()}'";
                }

                if (dateEdit年月度.DateTime != DateTime.MinValue)
                {

                    where += $" and ddate >= '{dateEdit年月度.DateTime.ToString("yyyy-MM-dd")}'";
                }

                if (dateEdit结束.DateTime != DateTime.MinValue)
                {
                    where += $" and ddate < '{dateEdit结束.DateTime.AddDays(1).ToString("yyyy-MM-dd")}'";
                }

                string sql = $@"insert into  {tbname}  (账套,类型,dPODate,csocode,客户, cInvCode,存货名称,项目类别,价税合计,项目预算,费用类型,tx)
                               select  '99','99',ddate,csocode,客户 ,cInvCode, 存货名称 , 项目类别 ,sum(价税合计) ,sum(项目预算),'',''
                                FROM View_销售订单2 where 1=1 {where} 
                                    group by ddate,csocode,客户 ,cInvCode, 存货名称 , 项目类别,csocode";
                SqlHelper.SqlHelper.ExecuteNonQuery(sql);
            }


          
        }

        private void U8用销售更新采购2(string tbname)
        {
            // 1) 构造 where & 参数
            var where = new StringBuilder("1=1");
            var ps = new List<SqlParameter>();

            if (!string.IsNullOrWhiteSpace(uiTextBox项目号开始.Text))
            {
                where.Append($" AND csocode >= '{uiTextBox项目号开始.Text.Trim()}'");
            }
            if (!string.IsNullOrWhiteSpace(uiTextBox项目号结束.Text))
            {
                where.Append($" AND csocode <= '{uiTextBox项目号结束.Text.Trim()}'");
            }
            if (dateEdit年月度.DateTime != DateTime.MinValue)
            {
                where.Append($" AND ddate >= '{dateEdit年月度.DateTime.ToString("yyyy-MM-dd")}'");
            }
            if (dateEdit结束.DateTime != DateTime.MinValue)
            {
                // 右开区间：< nextDay
                where.Append($" AND ddate < '{dateEdit结束.DateTime.Date.AddDays(1)}'");
            }

            // 2) 源数据：按你插入分支的口径进行汇总
            //    （如果你想“每个 csocode 只取一行（价税合计、项目预算 最大）”，
            //     把 CTE 换成 ROW_NUMBER 取 RowNum=1 的那种，再把 group by 去掉即可）
            string cteSrc = $@"
;WITH src AS (
    SELECT 
        ddate,
        csocode,
        客户,
        cInvCode,
        存货名称,
        项目类别,
        SUM(价税合计) AS 价税合计,
        SUM(项目预算) AS 项目预算
    FROM View_销售订单2
    WHERE {where}
    GROUP BY ddate, csocode, 客户, cInvCode, 存货名称, 项目类别
)";

            // 3) 先更新：仅更新“目标已存在(key 匹配)”的行
            string sqlUpdate = $@"
{cteSrc}
UPDATE t
SET 
    t.dPODate    = s.ddate,
    t.客户       = s.客户,
    t.存货名称   = s.存货名称,
    t.项目类别   = s.项目类别,
    t.价税合计   = s.价税合计,
    t.项目预算   = s.项目预算
FROM {tbname} t
JOIN src s
  ON t.csocode = s.csocode;";

            // 4) 再插入：只插入“目标不存在”的 key
            string sqlInsert = $@"
{cteSrc}
INSERT INTO {tbname}
(账套, 类型, dPODate, csocode, 客户, cInvCode, 存货名称, 项目类别, 价税合计, 项目预算, 费用类型, tx)
SELECT 
    '99','99', s.ddate, s.csocode, s.客户, s.cInvCode, s.存货名称, s.项目类别, s.价税合计, s.项目预算, '', ''
FROM src s
WHERE NOT EXISTS (
    SELECT 1 FROM {tbname} t
    WHERE t.csocode = s.csocode
      AND t.cInvCode = s.cInvCode
);";

            // 5) 事务执行（可选）
            SqlHelper.SqlHelper.ExecuteNonQuery(sqlUpdate);
            SqlHelper.SqlHelper.ExecuteNonQuery(sqlInsert);
        }

        private void simpleButton导出_Click(object sender, EventArgs e)
        {
            string path;
            saveFileDialog1.Filter = "Excel文件 (*.XLSX)|*.XLSX";
            if (this.saveFileDialog1.ShowDialog(this) == DialogResult.OK)
            {
                path = this.saveFileDialog1.FileName;
                if (!File.Exists(path))
                {
                    //MiniExcel.SaveAs(path, table, true, "sheet1", ExcelType.XLSX);

                    gridView1.ExportToXlsx(path);
                    MessageBox.Show("导出结束!");

                }
                else
                {
                    MessageBox.Show("文件已经存在,请重试!", "提示", MessageBoxButtons.OK);
                    return;
                }

            }
        }

        private void gridView1_CellMerge(object sender, DevExpress.XtraGrid.Views.Grid.CellMergeEventArgs e)
        {

            if (radioGroup1.EditValue.ToString() == "明细")
            {
                e.Merge = false;
                e.Handled = true;
            }
            if (radioGroup1.EditValue.ToString() == "汇总1" )
            {
                if (e.Column.FieldName == "项目编号" || e.Column.FieldName == "计划总金额" || e.Column.FieldName == "实际总金额"
                       || e.Column.FieldName == "TX" || e.Column.FieldName == "计划金额"
                      || e.Column.FieldName == "实际金额" || e.Column.FieldName == "类型" || e.Column.FieldName == "计划金额1"
                      || e.Column.FieldName == "实际金额1" )
                {
                    string 项目编号1 = gridView1.GetRowCellValue(e.RowHandle2 - 1, "项目编号").ToString();
                    string 项目编号2 = gridView1.GetRowCellValue(e.RowHandle2, "项目编号").ToString();

                    string 计划总金额1 = gridView1.GetRowCellValue(e.RowHandle2 - 1, "计划总金额").ToString();
                    string 计划总金额2 = gridView1.GetRowCellValue(e.RowHandle2, "计划总金额").ToString();

                    string 实际总金额1 = gridView1.GetRowCellValue(e.RowHandle2 - 1, "实际总金额").ToString();
                    string 实际总金额2 = gridView1.GetRowCellValue(e.RowHandle2, "实际总金额").ToString();

                    string TX1 = gridView1.GetRowCellValue(e.RowHandle2 - 1, "TX").ToString();
                    string TX2 = gridView1.GetRowCellValue(e.RowHandle2, "TX").ToString();

                    string 计划金额1 = gridView1.GetRowCellValue(e.RowHandle2 - 1, "计划金额").ToString();
                    string 计划金额2 = gridView1.GetRowCellValue(e.RowHandle2, "计划金额").ToString();

                    string 实际金额1 = gridView1.GetRowCellValue(e.RowHandle2 - 1, "实际金额").ToString();
                    string 实际金额2 = gridView1.GetRowCellValue(e.RowHandle2, "实际金额").ToString();


                    string 计划金额11 = gridView1.GetRowCellValue(e.RowHandle2 - 1, "计划金额1").ToString();
                    string 计划金额12 = gridView1.GetRowCellValue(e.RowHandle2, "计划金额1").ToString();

                    string 实际金额11 = gridView1.GetRowCellValue(e.RowHandle2 - 1, "实际金额1").ToString();
                    string 实际金额12 = gridView1.GetRowCellValue(e.RowHandle2, "实际金额1").ToString();

                    string 类型1 = gridView1.GetRowCellValue(e.RowHandle2 - 1, "类型").ToString();
                    string 类型2 = gridView1.GetRowCellValue(e.RowHandle2, "类型").ToString();

                    string 费用类型1 = gridView1.GetRowCellValue(e.RowHandle2 - 1, "费用类型").ToString();
                    string 费用类型2 = gridView1.GetRowCellValue(e.RowHandle2, "费用类型").ToString();


                    if (e.Column.FieldName == "项目编号")
                    {
                        if (项目编号1 == 项目编号2)
                        {
                            e.Merge = true;
                        }
                        else
                        {
                            e.Merge = false;
                        }
                    }

                    if (e.Column.FieldName == "计划总金额")
                    {
                        if (项目编号1 == 项目编号2 && 计划总金额1 == 计划总金额2)
                        {
                            e.Merge = true;
                        }
                        else
                        {
                            e.Merge = false;
                        }
                    }

                    if (e.Column.FieldName == "实际总金额")
                    {
                        if (项目编号1 == 项目编号2 && 实际总金额1 == 实际总金额2)
                        {
                            e.Merge = true;
                        }
                        else
                        {
                            e.Merge = false;
                        }
                    }

                    if (e.Column.FieldName == "TX")
                    {
                        if (项目编号1 == 项目编号2 && TX1 == TX2)
                        {
                            e.Merge = true;
                        }
                        else
                        {
                            e.Merge = false;
                        }
                    }

                    if (e.Column.FieldName == "计划金额")
                    {
                        if (项目编号1 == 项目编号2 && 计划金额1 == 计划金额2)
                        {
                            e.Merge = true;
                        }
                        else
                        {
                            e.Merge = false;
                        }
                    }

                    if (e.Column.FieldName == "实际金额")
                    {
                        if (项目编号1 == 项目编号2 && 实际金额1 == 实际金额2)
                        {
                            e.Merge = true;
                        }
                        else
                        {
                            e.Merge = false;
                        }

                    }
                    if (e.Column.FieldName == "计划金额1")
                    {
                        if (项目编号1 == 项目编号2 && 计划金额11 == 计划金额12)
                        {
                            e.Merge = true;
                        }
                        else
                        {
                            e.Merge = false;
                        }
                    }

                    if (e.Column.FieldName == "实际金额1")
                    {
                        if (项目编号1 == 项目编号2 && 实际金额11 == 实际金额12)
                        {
                            e.Merge = true;
                        }
                        else
                        {
                            e.Merge = false;
                        }

                    }


                    if (e.Column.FieldName == "类型")
                    {
                        if (项目编号1 == 项目编号2 && TX1 == TX2 && 类型1 == 类型2)
                        {
                            e.Merge = true;
                        }
                        else
                        {
                            e.Merge = false;
                        }
                    }

                    if (e.Column.FieldName == "费用类型")
                    {
                        if (项目编号1 == 项目编号2 && 费用类型1 == 费用类型2)
                        {
                            e.Merge = true;
                        }
                        else
                        {
                            e.Merge = false;
                        }
                    }
                }
                else
                {
                    e.Merge = false;
                }


            }
            if (radioGroup1.EditValue.ToString() == "汇总2")
            {
                if (e.Column.FieldName == "项目编号" || e.Column.FieldName == "计划总金额" || e.Column.FieldName == "实际总金额"
                     || e.Column.FieldName == "TX" || e.Column.FieldName == "工艺成本"
                    || e.Column.FieldName == "实际金额" || e.Column.FieldName == "类型" || e.Column.FieldName == "工艺成本1"
                    || e.Column.FieldName == "实际金额1" || e.Column.FieldName == "客户" || e.Column.FieldName == "存货名称" || e.Column.FieldName == "项目类别"
                      || e.Column.FieldName == "价税合计" || e.Column.FieldName == "项目预算" || e.Column.FieldName == "占比1" || e.Column.FieldName == "占比2")
                {
                    string 项目编号1 = gridView1.GetRowCellValue(e.RowHandle2 - 1, "项目编号").ToString();
                    string 项目编号2 = gridView1.GetRowCellValue(e.RowHandle2, "项目编号").ToString();

                    //string 计划总金额1 = gridView1.GetRowCellValue(e.RowHandle2 - 1, "计划总金额").ToString();
                    //string 计划总金额2 = gridView1.GetRowCellValue(e.RowHandle2, "计划总金额").ToString();

                    string 实际总金额1 = gridView1.GetRowCellValue(e.RowHandle2 - 1, "实际总金额").ToString();
                    string 实际总金额2 = gridView1.GetRowCellValue(e.RowHandle2, "实际总金额").ToString();

                    string TX1 = gridView1.GetRowCellValue(e.RowHandle2 - 1, "TX").ToString();
                    string TX2 = gridView1.GetRowCellValue(e.RowHandle2, "TX").ToString();

                    string 计划金额1 = gridView1.GetRowCellValue(e.RowHandle2 - 1, "工艺成本").ToString();
                    string 计划金额2 = gridView1.GetRowCellValue(e.RowHandle2, "工艺成本").ToString();

                    string 实际金额1 = gridView1.GetRowCellValue(e.RowHandle2 - 1, "实际金额").ToString();
                    string 实际金额2 = gridView1.GetRowCellValue(e.RowHandle2, "实际金额").ToString();


                    string 计划金额11 = gridView1.GetRowCellValue(e.RowHandle2 - 1, "工艺成本1").ToString();
                    string 计划金额12 = gridView1.GetRowCellValue(e.RowHandle2, "工艺成本1").ToString();

                    string 实际金额11 = gridView1.GetRowCellValue(e.RowHandle2 - 1, "实际金额1").ToString();
                    string 实际金额12 = gridView1.GetRowCellValue(e.RowHandle2, "实际金额1").ToString();

                    string 类型1 = gridView1.GetRowCellValue(e.RowHandle2 - 1, "类型").ToString();
                    string 类型2 = gridView1.GetRowCellValue(e.RowHandle2, "类型").ToString();

                    string 费用类型1 = gridView1.GetRowCellValue(e.RowHandle2 - 1, "费用类型").ToString();
                    string 费用类型2 = gridView1.GetRowCellValue(e.RowHandle2, "费用类型").ToString();

                    string 客户1 = gridView1.GetRowCellValue(e.RowHandle2 - 1, "客户").ToString();
                    string 客户2 = gridView1.GetRowCellValue(e.RowHandle2, "客户").ToString();

                    string 存货名称1 = gridView1.GetRowCellValue(e.RowHandle2 - 1, "存货名称").ToString();
                    string 存货名称2 = gridView1.GetRowCellValue(e.RowHandle2, "存货名称").ToString();

                    string 项目类别1 = gridView1.GetRowCellValue(e.RowHandle2 - 1, "项目类别").ToString();
                    string 项目类别2 = gridView1.GetRowCellValue(e.RowHandle2, "项目类别").ToString();

                    string 价税合计1 = gridView1.GetRowCellValue(e.RowHandle2 - 1, "价税合计").ToString();
                    string 价税合计2 = gridView1.GetRowCellValue(e.RowHandle2, "价税合计").ToString();

                    string 项目预算1 = gridView1.GetRowCellValue(e.RowHandle2 - 1, "项目预算").ToString();
                    string 项目预算2 = gridView1.GetRowCellValue(e.RowHandle2, "项目预算").ToString();

                    string 占比11 = gridView1.GetRowCellValue(e.RowHandle2 - 1, "占比1").ToString();
                    string 占比12 = gridView1.GetRowCellValue(e.RowHandle2, "占比1").ToString();

                    string 占比21 = gridView1.GetRowCellValue(e.RowHandle2 - 1, "占比2").ToString();
                    string 占比22 = gridView1.GetRowCellValue(e.RowHandle2, "占比2").ToString();

                    if (e.Column.FieldName == "项目编号")
                    {
                        if (项目编号1 == 项目编号2)
                        {
                            e.Merge = true;
                        }
                        else
                        {
                            e.Merge = false;
                        }
                    }

                    //if (e.Column.FieldName == "计划总金额")
                    //{
                    //    if (项目编号1 == 项目编号2 && 计划总金额1 == 计划总金额2)
                    //    {
                    //        e.Merge = true;
                    //    }
                    //    else
                    //    {
                    //        e.Merge = false;
                    //    }
                    //}

                    if (e.Column.FieldName == "实际总金额")
                    {
                        if (项目编号1 == 项目编号2 && 实际总金额1 == 实际总金额2)
                        {
                            e.Merge = true;
                        }
                        else
                        {
                            e.Merge = false;
                        }
                    }

                    if (e.Column.FieldName == "TX")
                    {
                        if (项目编号1 == 项目编号2 && TX1 == TX2)
                        {
                            e.Merge = true;
                        }
                        else
                        {
                            e.Merge = false;
                        }
                    }

                    if (e.Column.FieldName == "工艺成本")
                    {
                        if (项目编号1 == 项目编号2 && 计划金额1 == 计划金额2)
                        {
                            e.Merge = true;
                        }
                        else
                        {
                            e.Merge = false;
                        }
                    }

                    if (e.Column.FieldName == "实际金额")
                    {
                        if (项目编号1 == 项目编号2 && 实际金额1 == 实际金额2)
                        {
                            e.Merge = true;
                        }
                        else
                        {
                            e.Merge = false;
                        }

                    }
                    if (e.Column.FieldName == "工艺成本1")
                    {
                        if (项目编号1 == 项目编号2 && 计划金额11 == 计划金额12)
                        {
                            e.Merge = true;
                        }
                        else
                        {
                            e.Merge = false;
                        }
                    }

                    if (e.Column.FieldName == "实际金额1")
                    {
                        if (项目编号1 == 项目编号2 && 实际金额11 == 实际金额12)
                        {
                            e.Merge = true;
                        }
                        else
                        {
                            e.Merge = false;
                        }

                    }


                    if (e.Column.FieldName == "类型")
                    {
                        if (项目编号1 == 项目编号2 && TX1 == TX2 && 类型1 == 类型2)
                        {
                            e.Merge = true;
                        }
                        else
                        {
                            e.Merge = false;
                        }
                    }

                    if (e.Column.FieldName == "费用类型")
                    {
                        if (项目编号1 == 项目编号2 && 费用类型1 == 费用类型2)
                        {
                            e.Merge = true;
                        }
                        else
                        {
                            e.Merge = false;
                        }
                    }
               
                   

                    if (e.Column.FieldName == "客户")
                    {
                        if (项目编号1 == 项目编号2 && 客户1 == 客户2)
                        {
                            e.Merge = true;
                        }
                        else
                        {
                            e.Merge = false;
                        }
                    }

                    if (e.Column.FieldName == "存货名称")
                    {
                        if (项目编号1 == 项目编号2 && 存货名称1 == 存货名称2)
                        {
                            e.Merge = true;
                        }
                        else
                        {
                            e.Merge = false;
                        }
                    }


                    if (e.Column.FieldName == "项目类别")
                    {
                        if (项目编号1 == 项目编号2 && 项目类别1 == 项目类别2)
                        {
                            e.Merge = true;
                        }
                        else
                        {
                            e.Merge = false;
                        }
                    }

                    if (e.Column.FieldName == "价税合计")
                    {
                        if (项目编号1 == 项目编号2 && 价税合计1 == 价税合计2)
                        {
                            e.Merge = true;
                        }
                        else
                        {
                            e.Merge = false;
                        }
                    }
                    if (e.Column.FieldName == "项目预算")
                    {
                        if (项目编号1 == 项目编号2 && 项目预算1 == 项目预算2)
                        {
                            e.Merge = true;
                        }
                        else
                        {
                            e.Merge = false;
                        }
                    }

                    if (e.Column.FieldName == "占比1")
                    {
                        if (项目编号1 == 项目编号2 && 占比11 == 占比12)
                        {
                            e.Merge = true;
                        }
                        else
                        {
                            e.Merge = false;
                        }
                    }

                    //if (e.Column.FieldName == "占比2")
                    //{
                    //    if (项目编号1 == 项目编号2 && 占比21 == 占比22)
                    //    {
                    //        e.Merge = true;
                    //    }
                    //    else
                    //    {
                    //        e.Merge = false;
                    //    }
                    //}
                }
                else
                {
                    e.Merge = false;
                }
            }
            if (radioGroup1.EditValue.ToString() == "汇总3")
            {

            }

            e.Handled = true;
        }

        private void radioGroup1_SelectedIndexChanged(object sender, EventArgs e)
        {
            if (radioGroup1.EditValue.ToString() == "明细")
            {
                checkEdit设计费.Visible = false;
                checkEdit人工费.Visible = false;
                labelControl年度.Visible = false;
                dateEdit年月度.Visible = false;
                dateEdit结束.Visible = false;
                labelControl项目类别.Visible =false;
                lookUpEdit项目类别.Visible =false;
            }
            if (radioGroup1.EditValue.ToString() == "汇总1")
            {
                checkEdit设计费.Visible = false;
                checkEdit人工费.Visible = false;
                labelControl年度.Visible = false;
                dateEdit结束.Visible = false;
                dateEdit年月度.Visible = false;
                labelControl项目类别.Visible = false;
                lookUpEdit项目类别.Visible = false;
            }
            if (radioGroup1.EditValue.ToString() == "汇总2")
            {
                checkEdit设计费.Visible = true;
                checkEdit人工费.Visible = true;
                labelControl年度.Visible = true;
                dateEdit结束.Visible = true;
                dateEdit年月度.Visible = true;
                labelControl项目类别.Visible = true;
                lookUpEdit项目类别.Visible = true;
            }
            if (radioGroup1.EditValue.ToString() == "汇总3")
            {
                checkEdit设计费.Visible = true;
                checkEdit人工费.Visible = true;
                labelControl年度.Visible = true;
                dateEdit结束.Visible = true;
                dateEdit年月度.Visible = true;
                labelControl项目类别.Visible = true;
                lookUpEdit项目类别.Visible = true;
            }
        }

        private void Form1_Load(object sender, EventArgs e)
        {
            this.Text  = "报表查询" + Application.ProductVersion;
            radioGroup1.SelectedIndex = 0;
            DataTable tb = new DataTable();
            string sql = "select '全部' 项目类别 union all select distinct 项目类别 from View_采购订单2";
            tb = SqlHelper.SqlHelper.ExecuteDataset(sql).Tables[0];
           
            lookUpEdit项目类别.Properties.DataSource = tb;
            lookUpEdit项目类别.Properties.DisplayMember = "项目类别";
            lookUpEdit项目类别.Properties.ValueMember = "项目类别";
        }

        private void Form1_FormClosed(object sender, FormClosedEventArgs e)
        {
            Application.Exit();
        }
    }
}

